﻿Imports System.Data
Imports System.Data.SqlClient
Public Class ConAgenda
    'string de conexao
    'Dim strCon As String = "server=LAB10-13\SQLEXPRESS; integrated security=true;initial catalog=Agenda;"
    Dim strCon As String = "server=Adriano-PC; integrated security=true;initial catalog=Agenda;"
    Dim con As New SqlConnection(strCon)
    Function abreConexao() As Boolean
        Try
            If (con.State = ConnectionState.Closed) Then
                con.Open()
            End If
            Return True
        Catch ex As Exception
            Throw New Exception("Erro ao abrir conexão", ex)
            Return False
        End Try
    End Function

    Function fechaConexao() As Boolean
        Try
            If (con.State = ConnectionState.Open) Then
                con.Close()
            End If
            Return True
        Catch ex As Exception
            Throw New Exception("Erro ao fechar conexao", ex)
            Return False
        End Try
    End Function

    Function executaCmd(ByVal cmd As SqlCommand) As Integer
        Dim ret As Integer
        Try
            cmd.Connection = con
            abreConexao()
            ret = cmd.ExecuteNonQuery
            fechaConexao()
            ret = True
        Catch ex As Exception
            Throw New Exception("Erro ao executar comando", ex)
            ret = False
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function

    Function executaCmdScalar(ByVal cmd As SqlCommand) As String
        Dim ret As String
        Try
            cmd.Connection = con
            abreConexao()
            ret = cmd.ExecuteScalar
            fechaConexao()
        Catch ex As Exception
            Throw New Exception("Erro ao executar comando", ex)
            ret = ""
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
    Function executaConsulta(ByVal cmd As SqlCommand) As DataTable
        Dim tab As DataTable
        Dim ds As DataSet
        Dim da As SqlDataAdapter
        Try
            ds = New DataSet
            da = New SqlDataAdapter
            cmd.Connection = con
            da.SelectCommand = cmd
            da.Fill(ds, "tab")
            tab = ds.Tables("tab")
        Catch ex As Exception
            Throw New Exception("Erro ao consultar", ex)
        Finally
            da = Nothing
        End Try
        Return tab
    End Function
    'Function execultalogin(ByVal cmd As SqlCommand) As Boolean
    '    Dim adaptor As New SqlClient.SqlDataAdapter
    '    Dim dataset As New DataSet
    '    Try
    '        cmd.Connection = con
    '        abreConexao()
    '        adaptor.SelectCommand = cmd
    '        adaptor.Fill(dataset, "0")
    '        Dim count = dataset.Tables("0").Rows.Count
    '        If count > 0 Then
    '            frmLogin.status = dataset.Tables("0").Rows("0")(3)
    '            frmLogin.permissao = dataset.Tables("0").Rows("0")(4)
    '            frmLogin.logado = dataset.Tables("0").Rows("0")(1)
    '            frmLogin.codigo = dataset.Tables("0").Rows("0")(0)
    '            Return True
    '        Else
    '            Return False
    '        End If
    '        cmd = Nothing
    '    Catch ex As Exception
    '        Throw New Exception("Erro ao consultar", ex)
    '    Finally
    '        adaptor = Nothing
    '        dataset = Nothing
    '    End Try
    'End Function

    Function executaCmdAI(ByVal cmd As SqlCommand, ByVal tabela As String) As Integer
        Dim ret As Integer
        Try
            cmd.Connection = con
            abreConexao()
            cmd.CommandText &= ";select @@identity " & tabela & ";"
            ret = Convert.ToInt32(cmd.ExecuteScalar.ToString)
            fechaConexao()
        Catch ex As Exception
            Throw New Exception("Erro ao executar comando", ex)
            ret = -1
        Finally
            cmd = Nothing
        End Try
        Return ret

    End Function

    Function retdadosTab(ByVal cmd As SqlCommand) As DataTable
        Dim tab As DataTable
        Dim ds As DataSet
        Dim da As SqlDataAdapter
        Try
            cmd.Connection = con
            da = New SqlDataAdapter
            da.SelectCommand = cmd
            ds = New DataSet
            da.Fill(ds, "tab")
            tab = ds.Tables("tab")
        Catch ex As Exception
            Throw New Exception("Erro ao selecionar dados", ex)
        Finally
            da = Nothing
            ds = Nothing
        End Try
        Return tab
    End Function

    Function retdados(ByVal cmd As SqlCommand) As DataSet
        Dim ds As DataSet
        Dim da As SqlDataAdapter
        Try
            cmd.Connection = con
            da = New SqlDataAdapter
            da.SelectCommand = cmd
            ds = New DataSet
            da.Fill(ds, "tab")
        Catch ex As Exception
            Throw New Exception("Erro ao selecionar dados", ex)
        Finally
            da = Nothing
        End Try
        Return ds
    End Function

    Sub preencheCombo(ByVal comboBox As ComboBox, ByVal tabela As String, ByVal codigo As String, ByVal descricao As String, ByVal filtro As String, ByVal ordem As String)
        Dim cmd As SqlCommand
        Dim ds As DataSet
        Dim da As SqlDataAdapter
        Dim sql As String
        Try
            cmd = New SqlCommand
            cmd.Connection = con
            sql = "select * from " & tabela
            If (filtro <> "") Then
                sql = sql & " where " & filtro
            End If
            If (ordem <> "") Then
                sql = sql & " order by " & ordem
            End If
            cmd.CommandText = sql
            da = New SqlDataAdapter
            da.SelectCommand = cmd
            ds = New DataSet
            da.Fill(ds, "tab")
            comboBox.DataSource = ds.Tables(0)
            comboBox.DisplayMember = descricao
            comboBox.ValueMember = codigo
        Catch ex As Exception
            Throw New Exception("Erro ao carregar comobox", ex)
        Finally
            da = Nothing
            ds = Nothing
        End Try
    End Sub
End Class
